
const {firstworldupp,firstworlddown, toHump, createUrlParams} = require("../utils/utils");

const Controller = require('egg').Controller;

const {passwordDecode, passwordEncode} = require("../utils/passwordUtil");
const lodash = require('lodash')
const mysql = require('mysql2/promise');
const fs = require('fs')
const path = require('path')
const createTemplate = require('../utils/create_template')
const CURRENT_USER = "currentUser"

class DatabasesController extends Controller {
    async loginout() {
        this.ctx.session.currentUser = null
        var mysql = await this.getMysql2();
        mysql.close()
    }

    async init() {
        var {host, database, password, user, port} = this.ctx.request.body
        //初始化全局变量 数据库mysql 存储 connect对象
        if (!this.ctx.mysql2) {
            this.ctx.mysql2 = {}
        }
        //   this.ctx.body = 'Hello world';
        var a = await this.app.mysql.get("databse", {
            host, database, password, user, port: port
        })
        if (a) {
            this.ctx.session.currentUser = {
                host, database, password, user, port: port
            }
            var result = await this.testInit(host, database, password, user, port)
            console.log("已经有这个数据库了，直接登录")
            this.ctx.body = {
                token: passwordEncode(createUrlParams({
                    host, database, password, user, port
                }))
            }
            return false;
        }

        var result = await this.app.mysql.insert("databse", {
            host, database, password, user, port
        })
        //初始化用户数据库
        await this.testInit(host, database, password, user, port)
        if (result) {
            this.ctx.session.currentUser = {
                host, database, password, user, port: port
            }
        }

        this.ctx.body = {
            token: passwordEncode(createUrlParams({
                host, database, password, user, port
            }))
        }
    }

    async search() {
        var {keyword} = this.ctx.request.query

        const {database} = this.ctx.session.currentUser;

        var resultList = {}
        var tableList = require(`../public/${database}.json`);
        //如果没有keyword直接显示全部表格
        if (keyword) {
            tableList.map(item => {
                if (item.tableName.indexOf(keyword) >= 0) {
                    resultList[item.tableName] = item
                } else {
                    //查询fileds
                    var isTrue = item.fields.filter(filed => filed.name.indexOf(keyword) >= 0).length > 0
                    if (isTrue) {
                        resultList[item.tableName] = item
                    }
                }
            })
            //查询之后在查询 表之间的关系
            var list = lodash.map(resultList, item => item)
            for (let i = 0; i < list.length; i++) {
                var table = list[i]

                //默认设置全都清空 subField
                list[i].fields = list[i].fields.map(item => {
                    item.subField = null
                    return item
                })
                var bindFields = await this.app.mysql.select("bind_table", {
                    where: {
                        masterTable: table.tableName,
                        database
                    }
                })
                for (let j = 0; j < bindFields.length; j++) {
                    var bindOne = bindFields[j]
                    var subField = {
                        tableName: bindOne.subTable,
                        fieldName: bindOne.subfieldName,
                    };
                    //找到需要绑定的表的字段
                    var masterTable = bindOne.masterTable
                    var masterfieldName = bindOne.masterfieldName
                    if (!resultList[masterTable]) {
                        continue;
                    }
                    var fields = resultList[masterTable].fields
                    var fieldIndex = fields.findIndex(item => item.name == masterfieldName)
                    if (fieldIndex >= 0) {
                        resultList[masterTable].fields[fieldIndex].subField = subField
                    }

                }
            }
            this.ctx.body = resultList
        } else {
            this.ctx.body = tableList
        }


    }


    /*
    * 全都使用 getMysql2 获取数据库连接
    * 每一个key=[host, database]都会存储到session的mysql中
    * */
    async getMysql2(host, database, password, user, port) {
        if (!host) {
            host = this.ctx.session.currentUser.host;
            database = this.ctx.session.currentUser.database;
            password = this.ctx.session.currentUser.password;
            user = this.ctx.session.currentUser.user;
            port = this.ctx.session.currentUser.port;
        }
        var connection = await mysql.createPool({
            host, database, password, user, port,
            waitForConnections: true,
            connectionLimit: 10,
            queueLimit: 0
        })
        return connection
    }

    /*
    * 把数据库connect 放在  this.ctx.session.currentUser 中
    *
    * */
    async testInit(host, database, password, user, port) {
        //动态添加第二个数据库
        await this.getMysql2(host, database, password, user, port)
        var tableList = await this.getTableList(database)
        fs.writeFileSync(path.resolve(__dirname, `../public/${database}.json`), JSON.stringify(tableList))
        return tableList;
    }

    async getTableList(database) {
        var sql = `
        SELECT
    A.TABLE_SCHEMA '数据库',
    A.TABLE_NAME '表名',
    A.TABLE_ROWS '表记录行数',
    A.CREATE_TIME '创表时间',
    A.TABLE_COMMENT '表备注'
FROM INFORMATION_SCHEMA.TABLES A
WHERE
    A.TABLE_SCHEMA = '${database}'
    `
        var mysql2 = await this.getMysql2()
        const [rows] = await mysql2.execute(sql);
        var tables = lodash.uniq(rows.map(item => item.表名))
        var tableDescs = rows.map(item => item.表备注)
        var tableList = []
        for (let index = 0; index < tables.length; index++) {
            var fields = [/* name:"",nameBig.nameSmall,desc */];
            const tableName = tables[index];
            var fieldsSql = `select COLUMN_NAME ,COLUMN_COMMENT   from information_schema.COLUMNS where table_name = '${tableName}';  `
            var [fields] = await mysql2.execute(fieldsSql);
            var filedsMap = {}
            fields = fields.map(item => {
                filedsMap[item.COLUMN_NAME] = {
                    name: item.COLUMN_NAME,
                    nameBig: firstworlddown(toHump(item.COLUMN_NAME)),
                    nameSmall: firstworlddown(toHump(item.COLUMN_NAME)),
                    desc: item.COLUMN_COMMENT
                }
            })
            tableList.push({
                tableName,
                tableDesc: tableDescs[index],
                fields: lodash.map(filedsMap, item => item)
            })
            // console.log("fieldsSql result", result)
        }
        console.log("tableList", tableList)
        return tableList
        // this.ctx.session.tableList = tableList

    }


//bind 绑定表之间的字段
    async tableBind() {
        var {
            masterTable,
            masterfieldName,
            subTable,
            subfieldName,
        } = this.ctx.request.body
        var {database} = this.ctx.session.currentUser

        var one = await this.app.mysql.get("bind_table", {
            masterTable,
            masterfieldName,
            subTable,
            subfieldName,
            database
        })
        if (one) {
            this.ctx.body = {code: 400, message: "已经存在这个关系，不需要在绑定"};
            return false;
        }
        var result = await this.app.mysql.insert("bind_table", {
            masterTable,
            masterfieldName,
            subTable,
            subfieldName,
            database
        })
        this.ctx.body = result

    }

    async getBindFields() {
        var {
            masterTable,
        } = this.ctx.request.body
        var {database} = this.ctx.session.currentUser

        var binds = await this.app.mysql.select("bind_table", {
            where: {
                masterTable,
                database
            }
        })
        const mysql2 = await this.getMysql2()
        for (let i = 0; i < binds.length; i++) {
            var table1 = binds[i]
            var subTable = table1.subTable
            var subfieldName = table1.subfieldName

            var fieldsSql = `select COLUMN_NAME from information_schema.COLUMNS where table_name = '${subTable}';  `
            const [rows] = await mysql2.execute(fieldsSql);
            var fields = [];
            fields.push(...rows.map(item => {
                return {
                    name: item.COLUMN_NAME.toLowerCase(),
                    nameBig: firstworlddown(toHump(item.COLUMN_NAME.toLowerCase())),
                    nameSmall: firstworlddown(toHump(item.COLUMN_NAME.toLowerCase())),
                }
            }))
            binds[i].fields = fields

        }
        this.ctx.body = binds
    }

    async getDDLByTableName() {
        var {
            table,
        } = this.ctx.request.body
        var {database} = this.ctx.session.currentUser
        var mymysql2 = await this.getMysql2();
        var [rows] = await mymysql2.execute(`show create table ${table}`)
        this.ctx.body = rows[0]['Create Table'];
    }


    async getTableFields() {
        var {database} = this.ctx.session.currentUser
        const {tableName} = this.ctx.request.body
        var fieldsSql = `select  
  column_name "字段名称",
  column_type "字段类型长度",
  is_nullable "空标识",
  column_comment "字段说明" from information_schema.COLUMNS where table_name = '${tableName}';  `
        var mysql2 = await this.getMysql2();
        const [result] = await mysql2.execute(fieldsSql);

        //查询出来 居然字段会重复
        var resultMap = {}
        result.map(item => {
            resultMap[item.字段名称] = item
        })
        //补充外键关系

        var bindFields = await this.app.mysql.select("bind_table", {
            where: {
                masterTable: tableName,
                database
            }
        })


        this.ctx.body = {fields: lodash.map(resultMap, item => item), bindFields};
    }

    async ajaxLeftJoin() {
        var self = this;
        var {subValue, subfieldvalue} = this.ctx.request.body
        //处理外键 关系
        const {database} = this.ctx.session.currentUser
        /*  var waijianResultFeildList = await this.app.mysql.select(`bind_table`, {
              where: {
                  masterTable: tableName,
                  /!*   masterfieldName: "",
                     subTable: "",
                     subTableName: "",*!/
                  database,
              }
          })
          var waijianResultFeildListMap = {}
          waijianResultFeildList.map(item => {
              waijianResultFeildListMap[item.masterfieldName.toLowerCase()] = item
          })

          var rowKeys = Object.keys(row)
          for (let j = 0; j < rowKeys.length; j++) {
              var key = rowKeys[j]
              var value = row[key]
              if (waijianResultFeildListMap[key.toLowerCase()]) {
                  //把原版的字符串变成一个object
                  row[key] = {
                      value: value,
                      subFieldValue:
                  }
              }
          }
          subTable: "ems_exhibition"
  subfieldName: "id"
          */
        this.ctx.body = await self.findRowByTableNameAndId(subfieldvalue.subTable, subfieldvalue.subfieldName, subValue)
    }

    async getTableData() {
        var self = this;
        const {tableName, pageNum, pageSize, whereSql} = this.ctx.request.body
        var mysql = await this.getMysql2();
        var [rows] = await mysql.execute(`select * from ${tableName}  ${whereSql} LIMIT ${pageNum} , ${pageSize}`)
        var resultCont = await mysql.execute(`select count(*) from ${tableName} ${whereSql}`)

        //处理外键 关系
        const {database} = this.ctx.session.currentUser
        var waijianResultFeildList = await this.app.mysql.select(`bind_table`, {
            where: {
                masterTable: tableName,
                /*   masterfieldName: "",
                   subTable: "",
                   subTableName: "",*/
                database,
            }
        })
        var waijianResultFeildListMap = {}
        waijianResultFeildList.map(item => {
            waijianResultFeildListMap[item.masterfieldName.toLowerCase()] = item
        })
        for (let i = 0; i < rows.length; i++) {
            const row = rows[i]
            var rowKeys = Object.keys(row)
            for (let j = 0; j < rowKeys.length; j++) {
                var key = rowKeys[j]
                var value = row[key]
                if (waijianResultFeildListMap[key.toLowerCase()]) {
                    //把原版的字符串变成一个object
                    rows[i][key] = {
                        value: value,
                        //此字段存在关联
                        subFieldValue: waijianResultFeildListMap[key.toLowerCase()]
                    }
                }
            }
        }


        this.ctx.body = {
            result: rows,
            total: resultCont[0][0]['count(*)']
        }
    }

    async findRowByTableNameAndId(tableName, keyId, idValue) {
        if (typeof (idValue) == "number") {
            idValue = idValue + ""
        }
        var idsValues = idValue && idValue.split(',')
        var result = []
        if (idValue) {
            for (let i = 0; i < idsValues.length; i++) {
                var idvalueStr = idsValues[i]
                var mysql = await this.getMysql2();
                var [rows] = await mysql.execute(`select * from ${tableName} where ${keyId}='${idvalueStr}'  LIMIT 0,1`)
                var one = rows[0]
                result.push(one)
            }
        }
        return result
    }

    /*
    * 删除掉bind 关系
    * */
    async delSubField() {
        var {
            masterTable,
            masterfieldName,
            subTable,
            subfieldName
        } = this.ctx.request.body
        var one = await this.app.mysql.get('bind_table', {
            masterTable,
            masterfieldName,
            subTable,
            subfieldName
        })
        if (one) {
            await this.app.mysql.delete('bind_table', {
                id: one.id
            })
        }

        this.ctx.body = true

    }

    /*根据ddl生成 controller service view*/
    async createTemplate() {
        var {
            ddl
        } = this.ctx.request.body
        var result=await createTemplate(ddl)
        this.ctx.body = result
    }

    /*导出的模板打包成zip*/
    async exportZip() {
        var JSZip = require("jszip");
        var zip = new JSZip();

        var {
            tableName,ddl
        } = this.ctx.request.query

        // var mymysql2 = await this.getMysql2();
        // var [rows] = await mymysql2.execute(`show create table ${tableName}`)
        // var ddl = rows[0]['Create Table'];
        /*     return {
                 entity:entityResult,
                 mapper:mapperResult,
                 service:createService(tableName,definitions),
                 serviceImpl:createServiceImpl(tableName,definitions),
                 controller:createController(tableName,definitions),
                 vuelist:createVueList(tableName,definitions),
                 vuemodify:createVueModify(tableName,definitions),
                 jsjson:createJsJson(tableName,definitions)
             }*/
        var result = await createTemplate(decodeURIComponent(ddl))
        zip.file(`entity/${firstworldupp(toHump(tableName))}.java`, result.entity);
        zip.file(`mapper/${firstworldupp(toHump(tableName))}Mapper.java`, result.mapper);
        zip.file(`service/${firstworldupp(toHump(tableName))}Service.java`, result.service);
        zip.file(`service/impl/${firstworldupp(toHump(tableName))}ServiceImpl.java`, result.serviceImpl);
        zip.file(`controller/${firstworldupp(toHump(tableName))}Controller.java`, result.controller);
        zip.file(`views/${firstworldupp(toHump(tableName))}/list.vue`, result.vuelist);
        zip.file(`views/${firstworldupp(toHump(tableName))}/modify.vue`, result.vuemodify);
        zip.file(`json/${firstworldupp(toHump(tableName))}.json`,JSON.stringify(result.jsjson));
        zip.file(`views1/${firstworldupp(toHump(tableName))}/list.vue`,result.vuelist1);
        zip.file(`views1/${firstworldupp(toHump(tableName))}/modify.vue`,result.vuemodify1);
        zip.file(`views1/${firstworldupp(toHump(tableName))}/api.js`,result.api);

        var zip2path = () => {
            return new Promise(resolve => {
                zip
                    .generateNodeStream({type: 'nodebuffer', streamFiles: true})
                    .pipe(fs.createWriteStream(`${toHump(tableName)}.zip`))  //打包后的包名可以自己根据需求定义，路径可以根据需求更改
                    .on('finish', function () {
                        // JSZip generates a readable stream with a "end" event,
                        // but is piped here in a writable stream which emits a "finish" event.
                        console.log("out.zip written.");   //管道写完数据后，打印出提示
                        resolve()
                    });
            })
        }
        await zip2path();
        this.ctx.attachment(`${toHump(tableName)}.zip`);
        this.ctx.body = fs.createReadStream(path.resolve(__dirname, `../../${toHump(tableName)}.zip`));
          fs.unlink(`../${toHump(tableName)}.zip`, err => {
              if (err) throw err;
          })
        // co
    }

    async connectList() {
        this.ctx.body = await this.app.mysql.select("databse")
    }

}

module.exports = DatabasesController